package net.jsoft.pms.repository.task;

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import net.jsoft.platform.security.entity.user.SysUser;
import net.jsoft.pms.entity.plan.Plan;

@Transactional
public interface TaskRepository extends PagingAndSortingRepository<Plan, Long>, JpaSpecificationExecutor<Plan>
{
    @Query("from Plan where optPerson = :optPerson and lateFinish is null ")
    List<Plan> findProTask(@Param("optPerson") SysUser optPerson);

    @Query("from Plan where uniqueId = :uniqueId and optPerson = :user ")
    Plan findOneByUniqueID(@Param("uniqueId") long uniqueId, @Param("user") SysUser user);

    @Query("from Plan ")
    List<Plan> findAllPlan();

    @Query("from Plan where lateFinish is not null")
    List<Plan> findAllFinishedPlan();

    @Query("from Plan where lateFinish is null")
    List<Plan> findAllUnFinishedPlan();

    @Query("from Plan where (earlyStart <= :begin and  earlyFinish >= :begin) or (earlyStart between :begin and  :end) ")
    List<Plan> getPeriodTasks(@Param("begin") Date begin, @Param("end") Date end);

    @Query("from Plan where ((earlyStart <= :begin and  earlyFinish >= :begin) or (earlyStart between :begin and  :end) ) and lateFinish is not  null")
    List<Plan> getPeriodFinishedTasks(@Param("begin") Date begin, @Param("end") Date end);

    @Query("from Plan where ((earlyStart <= :begin and  earlyFinish >= :begin) or (earlyStart between :begin and  :end)) and lateFinish is null ")
    List<Plan> getPeriodUnFinishedTasks(@Param("begin") Date begin, @Param("end") Date end);

    @Query("from Plan where ((earlyStart <= :begin and  earlyFinish >= :begin) or (earlyStart between :begin and  :end)) and createDate <= :end")
    List<Plan> getWeekPlaned(@Param("begin") Date begin, @Param("end") Date end);

    @Query("from Plan where ((earlyStart <= :begin and  earlyFinish >= :begin) or (earlyStart between :begin and  :end) )and createDate > :end")
    List<Plan> getWeekUnPlaned(@Param("begin") Date begin, @Param("end") Date end);

    @Query("from Plan where (earlyStart <= '2017-01-01' and  earlyFinish >= '2017-01-01' )  or (earlyStart between '2017-01-01'  and  :end) and lateFinish  is null and earlyFinish < :end")
    List<Plan> delayTaskRisk(@Param("end") Date date);

    @Query("from Plan where  earlystart >= '2017-08-14' AND type in ('功能开发','接口开发','问题修复') AND earlyVersion is null")
    List<Plan> noVP();

    @Query(" from Plan where earlystart >= '2017-08-14' AND type IN ('功能开发','接口开发','问题修复') AND earlyVersion IS NOT NULL AND lateVersion IS NULL AND earlyVersion < :nowVersion")
    List<Plan> delayVPRisk(@Param("nowVersion") String nowVersion);

    @Query(value = " select p.* from t_dzswj_plan p join t_security_user s on p.optPerson = s.id  where  s.name = :user and createDate >= date_add((select lrrq from t_dzswj_ywfwqdb where id = :ywfwqdId) , interval -7 day) ", nativeQuery = true)
    List<Plan> queryPlanWithYwfwqdBefore7Days(@Param("user") String user, @Param("ywfwqdId") String ywfwqdId);

    @Query(value = " select p.* from t_dzswj_plan p join t_security_user s on p.optPerson = s.id  where  s.name = :user  ", nativeQuery = true)
    List<Plan> queryPlan(@Param("user") String user);

}
